package com.zzmcc.jms.controller;

import com.jfinal.aop.Before;
import com.jfinal.ext.kit.PoiExt;
import com.jfinal.ext.render.excel.JxlsRender;
import com.jfinal.kit.PathKit;
import com.jfinal.log.Logger;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.upload.UploadFile;
import com.zzmcc.base.BaseController;
import com.zzmcc.common.AuthorizeInterceptor;
import com.zzmcc.jms.model.Demand;
import com.zzmcc.jms.model.DemandOrder;
import com.zzmcc.jms.model.DemandScreen;
import com.zzmcc.jms.model.Project;
import com.zzmcc.sys.model.Userinfo;
import util.ChristStringUtil;
import util.ModelUtils;
import util.StringUtil;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

import static com.zzmcc.jms.common.ModelConfigWms.TABLE_Demand;

/**
 * 订单管理
 */
public class DemandOrderController extends BaseController {
    private static final Logger log =  Logger.getLogger(DemandOrderController.class);
 	public void index() {

        StringBuffer whee = new StringBuffer();
        List<Object> param = new ArrayList<Object>();
		String ctName = getPara("demand.ctName");
		String name = getPara("demand.name");
		String cusName = getPara("demand.cusName");
		String memo = getPara("demand.memo");
		String demandMemo = getPara("demand.demandMemo");
		String isOrder = getPara("demand.isOrder");
		String isProject = getPara("demand.isProject");
		String buildType = getPara("demand.buildType");

		String demandOwner = getPara("demand.demandOwner");
		String homeCounty = getPara("demand.homeCounty");

		if(ChristStringUtil.isNotEmpty(ctName)){
			whee.append(" and b.ctName like ?");
			param.add("%" + ctName + "%");
		}

        if(ChristStringUtil.isNotEmpty(name)){
            whee.append(" and name like ?");
            param.add("%" + name + "%");
        }

		if(ChristStringUtil.isNotEmpty(cusName)){
			whee.append(" and cusName like ?");
			param.add("%" + cusName + "%");
		}

		if(ChristStringUtil.isNotEmpty(demandMemo)){
			whee.append(" and demandMemo like ?");
			param.add("%" + demandMemo + "%");
		}

		if(ChristStringUtil.isNotEmpty(memo)){
			whee.append(" and memo like ?");
			param.add("%" + memo + "%");
		}

		if(ChristStringUtil.isNotEmpty(demandOwner)){
			whee.append(" and demandOwner = ?");
			param.add(demandOwner);
		}

		if(ChristStringUtil.isNotEmpty(homeCounty)){
			whee.append(" and homeCounty = ?");
			param.add(homeCounty);
		}

		if(ChristStringUtil.isNotEmpty(isProject)){
			whee.append(" and COALESCE(a.isProject,'否') = ?");
			param.add(isProject);
		}


		if(ChristStringUtil.isNotEmpty(isOrder)){
			whee.append(" and COALESCE(b.isOrder,'否') = ?");
			param.add(isOrder);
		}

		if(ChristStringUtil.isNotEmpty(buildType)){
			whee.append(" and buildType = ?");
			param.add(buildType);
		}

		Page<DemandOrder> pager = DemandOrder.dao.paginate(
				getParaToInt("pageNum", 1),
				getParaToInt("numPerPage", 20),
				"select a.id,a.did,a.name,a.homeCounty,a.cusName,a.isProject,\n" +
						"b.isOrder,b.createDate orderDate,b.ctName,b.buildType,b.submitDate,b.longitude,b.latitude,b.addr,\n" +
						"b.productType,b.productConfig,b.systemCnt,b.aerialHeight,b.aerialCnt,b.space,b.power,b.elect,b.memo ",
				" from jms_demand a left join jms_demand_order b \n" +
						" on b.did=a.did " +
						" where a.isConfirm='是'  "+ whee.toString()+" order by b.modifyDate desc",
				param.toArray());

		setAttr("pager",pager);
		setAttr("ctName",ctName);
		setAttr("name",name);
		setAttr("cusName",cusName);
		setAttr("memo",memo);
		setAttr("demandMemo",demandMemo);
		setAttr("demandOwner",demandOwner);
		setAttr("isOrder",isOrder);
		setAttr("isProject",isProject);
		setAttr("buildType",buildType);

		setAttr("homeCounty",homeCounty);
		render("../demand_order_list.html");

	}

	@Before(AuthorizeInterceptor.class)
	public void add() {
		String id = getPara("id");

		String sql="select a.id,a.did,a.name,a.homeCounty,a.cusName," +
				"b.id bid,b.isOrder,b.createDate,b.ctName,b.buildType,b.submitDate,b.longitude,b.latitude,b.addr,b.productType\n" +
				" from jms_demand a left join jms_demand_order b on b.did=a.did " +
				" where a.id=?";
		DemandOrder m= DemandOrder.dao.findFirst(sql, id);
		setAttr("demandOrder", m);
		render("../demand_order_input.html");
	}


	public void save() {
		DemandOrder s = getModel(DemandOrder.class, "demandOrder");
		ModelUtils.fillForUpdate(s);
		if (s.get("id")==null)
			s.save();
		else
			s.update();

		renderDWZSuccessJson("订单信息修改成功！");
	}


	public void export() {

		StringBuffer whee = new StringBuffer();
		List<Object> param = new ArrayList<Object>();

		String ctName = getPara("demand.ctName");
		String name = getPara("demand.name");
		String cusName = getPara("demand.cusName");
		String memo = getPara("demand.memo");
		String demandMemo = getPara("demand.demandMemo");
		String isOrder = getPara("demand.isOrder");
		String buildType = getPara("demand.buildType");
		String demandOwner = getPara("demand.demandOwner");
		String homeCounty = getPara("demand.homeCounty");

		if(ChristStringUtil.isNotEmpty(ctName)){
			whee.append(" and b.ctName like ?");
			param.add("%" + ctName + "%");
		}

		if(ChristStringUtil.isNotEmpty(name)){
			whee.append(" and name like ?");
			param.add("%" + name + "%");
		}

		if(ChristStringUtil.isNotEmpty(cusName)){
			whee.append(" and cusName like ?");
			param.add("%" + cusName + "%");
		}

		if(ChristStringUtil.isNotEmpty(demandMemo)){
			whee.append(" and demandMemo like ?");
			param.add("%" + demandMemo + "%");
		}

		if(ChristStringUtil.isNotEmpty(memo)){
			whee.append(" and memo like ?");
			param.add("%" + memo + "%");
		}

		if(ChristStringUtil.isNotEmpty(demandOwner)){
			whee.append(" and demandOwner = ?");
			param.add(demandOwner);
		}

		if(ChristStringUtil.isNotEmpty(homeCounty)){
			whee.append(" and homeCounty = ?");
			param.add(homeCounty);
		}
		if(ChristStringUtil.isNotEmpty(isOrder)){
			whee.append(" and COALESCE(isOrder,'否') = ?");
			param.add(isOrder);
		}

		/**
		 * 清单
		 */
		String sql="select a.did,a.name,a.homeCounty," +
				"b.ctName,b.buildType,b.submitDate,b.longitude,b.latitude,b.addr,\n" +
				"b.productType,b.productConfig,b.systemCnt,b.aerialHeight,b.aerialCnt,b.space,b.power,b.elect,b.memo  \n"+
				" from jms_demand a\n" +
				"left join\n" +
				"jms_demand_order b\n" +
				"on a.did=b.did\n" +
				"where a.isConfirm='是' "+ whee.toString()+" order by b.modifyDate desc";
		List<Record> orderDetail = Db.find(sql, param.toArray());

		Map<String, Object> beans = new HashMap<String, Object>();
		beans.put("orderDetail", orderDetail);

		String templateFileName = PathKit.getRootClassPath() + "/tpl_imp_order.xls";
		String filename = "demand_order_imp.xls";
		render(JxlsRender.me(templateFileName).filename(filename).beans(beans));

	}

	public void imp() {
		render("../demand_order_imp.html");
	}


	public void impData() {
		String path = new SimpleDateFormat("yyyy/MM/dd").format(new Date());
		UploadFile file = getFile();
		String fileName = null;
		if (file !=null) {
			File source = file.getFile();
			fileName = file.getFileName();
			try {
				FileInputStream fis = new FileInputStream(source);
				File targetDir = new File(PathKit.getWebRootPath()  + "/u/" + path);
				if (!targetDir.exists()) {
					targetDir.mkdirs();
				}
				File target = new File(targetDir, fileName);
				if (!target.exists()) {
					target.createNewFile();
				}
				FileOutputStream fos = new FileOutputStream(target);
				byte[] bts = new byte[300];
				while (fis.read(bts, 0, 300) != -1) {
					fos.write(bts, 0, 300);
				}
				fos.close();
				fis.close();
				source.delete();
				//1.清空临时表
				Db.update("delete a from imp_demand_order a");
				String templateFileName = PathKit.getWebRootPath()  + "/u/" + path +"/" +fileName;
				//2.导入数据
				String sql = "insert into imp_demand_order (did,homeCounty,ctName,name,buildType,submitDate,longitude,latitude," +
						"addr,productType,productConfig,systemCnt,aerialHeight,aerialCnt," +
						"space,power,elect,memo) " +
						"values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
				Map<String, Object> map = PoiExt.ReadExcel(templateFileName, "铁塔需求订单确认表", sql,6,1, 18);
				renderDWZSuccessJson("总记录数:"+map.get("count")+"；成功导入:"+map.get("success")+"；失败数"+map.get("lost"));
				//3.更新筛查结果
				List<Record> records =Db.find("select * from imp_demand_order ");
				for(Record r:records) {
					sql="INSERT INTO jms_demand_order (did,ctName,buildType,submitDate,longitude,latitude,addr,space,aerialHeight,aerialCnt,productType,productConfig,systemCnt,power,elect,memo) \n" +
							"VALUES("+r.get("did")+","+r.get("ctName")+","+r.get("buildType")+","+r.get("submitDate")+",\n" +
							""+r.get("longitude")+","+r.get("latitude")+","+r.get("addr")+","+r.get("space")+","+r.get("aerialHeight")+","+r.get("aerialCnt")+",\n" +
							""+r.get("productType")+","+r.get("productConfig")+","+r.get("systemCnt")+","+r.get("power")+","+r.get("elect")+","+r.get("memo")+")\n" +
							"ON DUPLICATE KEY UPDATE ctName="+r.get("ctName")+",buildType="+r.get("buildType")+",submitDate="+r.get("submitDate")+",\n" +
							"longitude="+r.get("longitude")+",latitude="+r.get("latitude")+",addr="+r.get("addr")+",\n" +
							"space="+r.get("space")+",aerialHeight="+r.get("aerialHeight")+",aerialCnt="+r.get("aerialCnt")+",\n" +
							"productType="+r.get("productType")+",productConfig="+r.get("productConfig")+",systemCnt="+r.get("systemCnt")+",power="+r.get("power")+",elect="+r.get("elect")+",memo="+r.get("memo")+"";
					Db.update(sql);
					//更新筛查状态
					sql="update jms_demand a set a.isOrder=是 where did=?";
					Db.update(sql,r.get("did"));
				}
			} catch (FileNotFoundException e) {
				renderDWZErrorJson("上传出现错误，请稍后再上传");
			}catch (Exception e){
				renderDWZErrorJson("导入失败，请重新上传"+e.getMessage());
			}
		}

	}

	public  void transProject(){
		String[] ids = getParaValues("ids");
		int i=0;
		StringBuilder sb = new StringBuilder(" where a.id in (");
		for (String id : ids) {
			i+=1;
			sb.append(id);
			if (i<ids.length)
				sb.append(",");
		}
		sb.append(")");
		String sql="SELECT a.did,a.pid,b.ctName from jms_demand a\n" +
				"LEFT JOIN jms_demand_order b\n" +
				"on a.did=b.did"+sb.toString();
		Demand m =  Demand.dao.findFirst(sql);
		String pid =m.get("pid");
		sql="INSERT INTO jms_project (pid,name) \n" +
				"VALUES('"+pid+"','"+m.get("ctName")+"' )\n" +
				"ON DUPLICATE KEY UPDATE name='"+m.get("ctName")+"'\n" ;
		Db.update(sql);
		//合并需求的项目id
		sql  = "update jms_demand a set a.isProject='是',a.pid="+pid+sb.toString();
		Db.update(sql);
		renderDWZSuccessJson("需求转项目处理成功!");
	}


	public void batch() {
		//需求确认
		String[] ids = getParaValues("ids");
		for(String id:ids) {
			String sql="SELECT a.did,a.pid,b.ctName from jms_demand a\n" +
					"LEFT JOIN jms_demand_order b\n" +
					"on a.did=b.did where a.id = ? ";
			Demand m =  Demand.dao.findFirst(sql,id);
			String pid =m.get("pid");
			sql="INSERT INTO jms_project (pid,name) \n" +
					"VALUES('"+pid+"','"+m.get("ctName")+"' )\n" +
					"ON DUPLICATE KEY UPDATE name='"+m.get("ctName")+"'\n" ;
			Db.update(sql);
			//合并需求的项目id
			sql  = "update jms_demand a set a.isProject='是' where id=? ";
			Db.update(sql,id);
		}
		renderDWZSuccessJson("需求转项目处理成功!");

	}


	@Before(AuthorizeInterceptor.class)
	public void delete() {

		String[] ids = getParaValues("ids");
		for(String id:ids){
			Demand.dao.deleteById(id);
		}
		renderDWZSuccessJson("删除成功!");

	}


}
